package com.zy.dao.impl;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.junit.Test;

import com.sun.org.apache.regexp.internal.recompile;
import com.zy.dao.LinkManDao;
import com.zy.entity.Customer;
import com.zy.entity.Dict;
import com.zy.entity.Linkman;
import com.zy.utils.JDBCutil;

public class LinkManDaoImpl implements LinkManDao{

	@Override
	public List<Customer> getCustomer() {
		Connection con = null;
		String sql = "SELECT * from t_customer";

		try {
			// 从配置文件里面取值
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			ResultSet rs = ps.executeQuery();

			// 创建集合
			ArrayList<Customer> arrayList = new ArrayList<>();
			// 填充集合
			while (rs.next()) {
				Customer customer = new Customer();
				customer.setCid(rs.getInt(1));
				customer.setCustName(rs.getString(2));
				//--------------------------------------------
				customer.getDict().setDname(rs.getString(3));
				//----------------------------------------------
				customer.setCustSource(rs.getString(4));
				customer.setCustPhone(rs.getString(5));
				customer.setCustMobile(rs.getString(6));
				//装入集合
				arrayList.add(customer);

			}
			// 返回list
			return arrayList;

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void insertLinkMan(Linkman linkman) {
		try {
			String sql = "insert into t_linkman values(null,?,?,?,?,?)";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setString(1, linkman.getLinkName());
			ps.setString(2, linkman.getLinkGender());
			ps.setString(3, linkman.getLinkPhone());
			ps.setString(4, linkman.getLinkMobile());
			ps.setInt(5, linkman.getCustomer().getCid());
			
			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public List<Linkman> quaryall() {
		Connection con = null;
		String sql = "select linkid,linkName,linkGender,linkPhone,linkMobile,custName from t_linkman INNER JOIN t_customer on clid=cid";

		try {
			// 从配置文件里面取值
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			ResultSet rs = ps.executeQuery();

			// 创建集合
			ArrayList<Linkman> arrayList = new ArrayList<>();
			// 填充集合
			while (rs.next()) {
				Linkman linkman = new Linkman();
				linkman.setLinkid(rs.getInt(1));
				linkman.setLinkName(rs.getString(2));
				linkman.setLinkGender(rs.getString(3));
				linkman.setLinkPhone(rs.getString(4));
				linkman.setLinkMobile(rs.getString(5));
				//报错==在linkman中创建Customer的对象时，没有进行初始化，所以出现了空指针的错误=========
				linkman.getCustomer().setCustName(rs.getString(6));
				
				arrayList.add(linkman);

			}
			// 返回list
			return arrayList;

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Test
	public void tt() {
		List<Linkman> linkmans = quaryall();
		for (Linkman linkman : linkmans) {
			System.out.println(linkman.toString());
		}
	}

	@Override
	public Linkman quaryLinkman(int id) {
		try {
			String sql = "select * from t_linkman where linkid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setInt(1, id);
			
			ResultSet rs = ps.executeQuery();
			Linkman linkman = new Linkman();
			

			while (rs.next()) {
				linkman.setLinkid(rs.getInt(1));
				linkman.setLinkName(rs.getString(2));
				linkman.setLinkGender(rs.getString(3));
				linkman.setLinkPhone(rs.getString(4));
				linkman.setLinkMobile(rs.getString(5));
				linkman.getCustomer().setCid(rs.getInt(6));
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return linkman;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void updateLinkman(Linkman linkman) {
		try {
			String sql = "update t_linkman set linkName=?,linkGender=?,linkPhone=?,linkMobile=?,clid=? where linkid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setString(1, linkman.getLinkName());
			ps.setString(2, linkman.getLinkGender());
			ps.setString(3, linkman.getLinkPhone());
			ps.setString(4, linkman.getLinkMobile());
			ps.setInt(5,  linkman.getCustomer().getCid());
			ps.setInt(6, linkman.getLinkid());
			
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public List<Linkman> quaryByName(String name) {
		try {
			String str = "'%"+name+"%'";
			String sql = "select linkid,linkName,linkGender,linkPhone,linkMobile,custName from t_linkman INNER JOIN t_customer on clid=cid where linkName like "+str;
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			
			
			ResultSet rs = ps.executeQuery();
			List<Linkman> linkmans = new ArrayList<>();
			

			while (rs.next()) {
				Linkman linkman = new Linkman();
				linkman.setLinkid(rs.getInt(1));
				linkman.setLinkName(rs.getString(2));
				linkman.setLinkGender(rs.getString(3));
				linkman.setLinkPhone(rs.getString(4));
				linkman.setLinkMobile(rs.getString(5));
				linkman.getCustomer().setCustName(rs.getString(6));
				
				linkmans.add(linkman);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return linkmans;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Linkman> quaryByCustomer(int clid) {
		try {
			String sql = "select linkid,linkName,linkGender,linkPhone,linkMobile,custName from t_linkman INNER JOIN t_customer on clid=cid where clid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			ps.setInt(1, clid);
			
			ResultSet rs = ps.executeQuery();
			List<Linkman> linkmans = new ArrayList<>();
			

			while (rs.next()) {
				Linkman linkman = new Linkman();
				linkman.setLinkid(rs.getInt(1));
				linkman.setLinkName(rs.getString(2));
				linkman.setLinkGender(rs.getString(3));
				linkman.setLinkPhone(rs.getString(4));
				linkman.setLinkMobile(rs.getString(5));
				linkman.getCustomer().setCustName(rs.getString(6));
				
				linkmans.add(linkman);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return linkmans;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Linkman> quaryByCL(int clid, String linkname) {
		try {
			String str = "'%"+linkname+"%'";
			String sql = "select linkid,linkName,linkGender,linkPhone,linkMobile,custName from t_linkman INNER JOIN t_customer on clid=cid where clid=? and linkName like "+str;
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			ps.setInt(1, clid);
			
			ResultSet rs = ps.executeQuery();
			List<Linkman> linkmans = new ArrayList<>();
			

			while (rs.next()) {
				Linkman linkman = new Linkman();
				linkman.setLinkid(rs.getInt(1));
				linkman.setLinkName(rs.getString(2));
				linkman.setLinkGender(rs.getString(3));
				linkman.setLinkPhone(rs.getString(4));
				linkman.setLinkMobile(rs.getString(5));
				linkman.getCustomer().setCustName(rs.getString(6));
				
				linkmans.add(linkman);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return linkmans;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	

}
